/*
 * DBProc.java
 *
 * Created on March 8, 2007, 4:05 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package wap.rockvilletech.portal;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.log4j.Logger;

/**
 *
 * @author Usman
 */
public class DBProc {

    private static final Logger log = Logger.getLogger(DBProc.class);
    private static DBProc dbProc = null;
    private static Hashtable<String, String> queries;
    private static int connCount = 0;
    private Vector<String> vecMetaDataColumnLabels;

    /** Creates a new instance of DBProc */
    private DBProc() {
        queries = new Hashtable<String, String>();
        //////////////////////////////////////PORTAL///////////////////////////////////////////
        queries.put("GetAppData", "SELECT CategoryImage,SubCategoryPgCss,CategoryName,SubCategoryPgTxtColour,SubCategoryFooter,SubCategoryHeader,TopCatName,FooterTxt,ParentCategoryID,PageTitle,CategoryType,FilmOrSinger,MainCat,subcategories FROM categories WHERE CategoryName LIKE '${App}' and Status = 1 ORDER BY Priority ASC");
        queries.put("GetAppCategories", "SELECT CategoryId,CategoryImage,subcategories,CategoryName,ParentCategoryId,PageTitle, CategoryType, FilmOrSinger, MainCat FROM categories WHERE ParentCategoryId=(SELECT CategoryId FROM  categories WHERE CategoryName Like '${App}') AND Status = 1 ORDER BY priority ASC");
        queries.put("GetContent", "SELECT Distinct ContentId, ContentName, ContentImage, Price, ServiceURL, jspf, html, CategoryID, OrderSequence, MainCat FROM content WHERE (Status = 1) AND (SingerOrFilm LIKE '%${FilmOrSinger}%') AND (CategoryType LIKE '%${CatType}%') AND (MainCat LIKE '%${MainCat}%') AND (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') ORDER BY ContentId desc limit ${Top} ");
        queries.put("GetTextContent", "SELECT distinct ContentId, ContentName, ContentImage, Price, ServiceURL, jspf, html, CategoryID, OrderSequence, Maincat FROM content WHERE (Status = 1 AND IsAuthorized = 1) AND (CategoryId = ${CatID}) ORDER BY Insertiontime desc, MainCat asc limit ${Top}");
        queries.put("GetTopContent", "SELECT content.ContentID as contid, content.ContentName as contname, "
                + " content.ContentImage as contimg,  content.Price as contprice, "
                + " content.ServiceURL as contserURL, content.jspf AS JSP, content.html AS HTM,  "
                + " content.Parameters as params, content.Status, content.SingerOrFilm, "
                + " content.CategoryType As ContType,  content.CategoryId As CategoryID, "
                + " content.MainCat As MainCat "
                + " FROM content,downloads  "
                + " WHERE content.SingerOrFilm like '%${SingerOrFilm}%'   "
                + " AND (content.CategoryType LIKE '%${CatType}%') "
                + " AND (content.MainCat LIKE '%${MainCat}%')  "
                + " AND content.contentid = downloads.contentid "
                + " AND content.Status = '1' AND content.IsAuthorized = 1 "
                + " Group by ContentName, ContentImage, content.Price, content.ServiceURL, "
                + " content.SingerOrFilm, content.SeqID, content.jspf,content.Html, content.Parameters, "
                + " content.Status, content.ContentID , "
                + " content.OrderSequence, content.CategoryType, content.MainCat "
                + " HAVING content.contentid IN ( "
                + " SELECT downloads.contentid "
                + " FROM downloads "
                + " where CategoryType LIKE content.CategoryType ${MainCatExp} ${SingerOrFilmExp} "
                + " GROUP BY downloads.contentid, downloads.CategoryType "
                + " ORDER BY content.Categorytype, COUNT(downloads.contentid) DESC limit 8"
                + " )"
                + " ORDER BY OrderSequence,content.Categorytype,COUNT(downloads.contentid)desc ");

        queries.put("GetTopContent2", "SELECT DISTINCT C1.ContentID as contid, C1.ContentName as contname, "
                + "C1.ContentImage as contimg, C1.Price as contprice, C1.ServiceURL as contserURL, "
                + "C1.jspf AS JSP, C1.html AS HTM, C1.Parameters as params, C1.Status, "
                + "C1.SingerOrFilm, C1.CategoryType As ContType, C1.CategoryId As CategoryID, "
                + "C1.MainCat As MainCat "
                + "FROM content AS C1, downloads "
                + "WHERE C1.SingerOrFilm like '%${SingerOrFilm}%' "
                + "AND (C1.CategoryType LIKE '%${CatType}%') "
                + "AND (C1.MainCat LIKE '%${MainCat}%') "
                + "AND C1.Status = 1 "
                + "Group by ContentName, ContentImage, C1.Price , C1.ServiceURL, "
                + "C1.SingerOrFilm, C1.SeqID, C1.jspf, C1.Html, C1.Parameters, "
                + "C1.Status, C1.ContentID , C1.OrderSequence, C1.CategoryType , C1.MainCat "
                + "HAVING C1.contentid IN ( "
                + "SELECT C2.contentid "
                + "FROM content as C2 "
                + "WHERE CategoryType LIKE C1.CategoryType "
                + "AND C2.MainCat=C1.MainCat "
                + "AND C2.SingerOrFilm = C1.SingerOrFilm "
                + "GROUP BY C2.contentid, C2.CategoryType "
                + "ORDER BY C1.Categorytype, (SELECT COUNT(ContentId) FROM downloads LIMIT 8) DESC ) "
                + "ORDER BY OrderSequence, C1.Categorytype, (SELECT COUNT(ContentId) FROM downloads) DESC;");

        queries.put("CountContent", "SELECT count(Distinct ContentId) FROM content WHERE  (Status = 1) AND (content.SingerOrFilm LIKE '%${SingerOrFilm}%') AND (content.CategoryType LIKE '%${CatType}%') AND (content.MainCat LIKE '%${MainCat}%') AND (content.Description LIKE '%${Tags}%' OR content.ContentName LIKE '%${Tags}%')");
        queries.put("CountTextContent", "SELECT count(Distinct ContentId) FROM content WHERE  (Status = 1) AND (CategoryId = ${CatID})");
        queries.put("GetAppTopCat", "SELECT  CategoryName FROM categories WHERE CategoryID LIKE '${ID}' ");
        queries.put("GetContentDetail", "Select Contentimage,ContentName,Price,CategoryName,CategoryID,Description FROM content WHERE contentid = '${CID}' and Status = 1 limit 1");
       //-- queries.put("LogVisit", "CALL page_Hits('${IP}', '${UA}', '${Type}', '${GSM}', '${APN}', '${Brand}', '${Model}', '${AppName}')");
        queries.put("GetHitsReport", "SELECT COUNT(*), CONVERT(varchar, LastUpdate, 111),Type FROM hits group by CONVERT(varchar, LastUpdate, 111),Type order by CONVERT(varchar, LastUpdate, 111) desc ");
        queries.put(
                "GetContentDetail_From_ContentId",
                "SELECT CO.ContentID, CO.ContentName, CO.CategoryID, "
                + " CO.CategoryType, CO.Price, CO.SingerOrFilm, CO.Description, CA.CategoryName AS CatName, CA.MainCat "
                + " FROM content CO, categories CA WHERE CO.ContentId = '${ContentId}' "
                + " AND CA.FilmOrSinger = CO.SingerOrFilm AND CO.Status = 1 AND CO.IsAuthorized = 1 LIMIT 1");

        queries.put("GetByContentSequence", "SELECT  Content.ContentID as contid, Content.ContentName as contname , "
                + " Content.ContentImage as contimg, Content.Price as contprice,  Content.ServiceURL as contserURL, "
                + " Content.jspf AS JSP, Content.html AS HTM, Content.Parameters as params, Content.Status, "
                + " Content.SingerOrFilm, Content.CategoryType As ContType, Content.CategoryId As CategoryID, "
                + " Content.MainCat As MainCat,Content.ContentSequence     "
                + " FROM content as content WHERE "
                + " Content.Status = 1 AND Content.IsAuthorized = 1   "
                + " Group by Content.ContentSequence ,content.MainCat,ContentName,ContentImage,Content.Price,Content.ServiceURL, "
                + " Content.SingerOrFilm,Content.SeqID,Content.jspf,Content.Html,Content.Parameters,Content.Status,Content.ContentID, "
                + " Content.OrderSequence, Content.CategoryType  "
                + " ORDER BY OrderSequence,content.Categorytype,Content.ContentSequence desc ");

        queries.put("GetByContentSequence2", "SELECT * FROM ( "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "MainCat As MainCat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 529 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' and "
                + "Status = 1 order by ContentSequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 7 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' and "
                + "Status = 1 order by ContentSequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 527 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' and "
                + "Status = 1 order by ContentSequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 528 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' and "
                + "Status = 1 order by ContentSequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 530 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' "
                + "AND Status = 1 order by ContentSequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 524 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' "
                + "AND Status = 1 ORDER BY contentsequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + " UNION ALL "
                + "(SELECT Distinct ContentID as contid, ContentName as contname , "
                + "ContentImage as contimg, Price as contprice, "
                + "ServiceURL as contserURL, jspf AS JSP, html AS HTM, "
                + "Parameters as params, Status, SingerOrFilm, "
                + "CategoryType As ContType, CategoryId As CategoryID, "
                + "maincat as maincat,ContentSequence,orderSequence, InsertionTime FROM content  "
                + "WHERE (Description LIKE '%${Tags}%' OR ContentName LIKE '%${Tags}%') AND categoryid = 525 and MainCat like '%${MainCat}%' and SingerOrFilm like '%${SingerOrFilm}%' "
                + "AND Status = 1 ORDER BY contentsequence desc, InsertionTime desc limit ${Dynamic_Limit}) "
                + ") AS res ORDER BY orderSequence asc, ContentSequence desc");

        //Quries copied from Aashiqbhatti
        queries.put("GETCONTENTDETAILS", "Select SeqId,ContentId,ContentName,CategoryId,CategoryName,OriginalFile,Price,Status,ProviderId,ContentImage,Format,Width,Height,AspectRatio,Singerorfilm,ServiceURL,Parameters,jspf,OrderSequence,Insertiontime,CategoryType,Description,SampleFile,ProvidercontentId from content where ContentID='${ContentID}' and Status=1;");
        queries.put("SAVEDOWNLOAD", "INSERT INTO downloads(ContentId,CategoryID,CategoryName,CategoryType,Price,ProviderId,PurchaseTime,MSISDN,Status,HandsetBrand,HandsetModel,Operator,SingerOrFilm,MainCat,TID) SELECT ContentID,CategoryID,CategoryName,CategoryType,Price,ProviderId,NOW(),'${GSM}',0,'${brand}','${model}','${Operator}',SingerOrFilm,MainCat,${TID} FROM content WHERE ContentId ='${ContentID}' limit 1");
        queries.put("GETDOWNLOADSTATUS", "Select ifnull(min(Status), -1) as dload_status from downloads WHERE ContentId='${ContentID}' AND MSISDN='${GSM}' AND CategoryType='${CategoryType}'");
        queries.put("GETCONTENTFILE", "Select ContentFile from handsetcontent where SeqId=${SeqId} and Status=1");
        queries.put("GETORIGINALPATH", "Select ContentOriginalPath from categories where SeqID='${categoryID}' and Status=1");
        queries.put("GETASPECTRATIOS", "Select SeqId,OriginalFile,AspectRatio from content where ContentId='${contentID}' and Status=1");
        queries.put("UPDOWNLOAD", "Update downloads set Status = Status+1 WHERE ContentId='${ContentID}'AND MSISDN='${GSM}'");
        queries.put("UPCHARGE_DLOAD_STATUS", "Update content_charge set status=status+1,price='${price}',ip='${ip}',brand='${brand}',model='${model}' WHERE seqid=${logId}");        
        queries.put("MGCDOWNLOAD", "Update downloads set ContentId='-404' WHERE ContentId='${ContentID}' AND MSISDN='${GSM}' AND Status=0");
        queries.put("GETTONEFORMATS", "Select SeqId,OriginalFile,Price,Format from content where ContentID='${contentID}' and CategoryId='${categoryID}' and Status=1");
        queries.put("GETGT", "Select c.SeqId,c.Price,hc.SeqId,hc.ContentFile from content c, handsetcontent hc where c.ContentId='${contentID}' and c.CategoryId='${categoryID}' and c.Status=1 and c.ContentId=hc.ContentId and hc.HandSetId=(Select HandSetId from handsets where HandSetBrand='${brand}' and HandSetModel='${model}') and hc.Status=1");
        queries.put("GETGTALTERNATE", "((Select c.CcontentId as SID,c.Price,c.CategoryId,c.ContentName,c.CategoryType from content c, handsetcontent hc where CategoryType like 'GAMES' and c.Status=1 and c.ContentId=hc.ContentId and hc.HandSetId=(Select HandSetId from handsets where HandSetBrand='${brand}' and HandSetModel='${model}') and hc.Status=1) Union (Select c.ContentId as SID,max(c.Price),max(c.CategoryId),max(c.ContentName),max(c.categorytype) from content c where CategoryType like 'POLYTONES' and Status=1 group by c.ContentId limit 5) Union (Select c.ContentId as SID,max(c.Price),max(c.categoryid),max(c.contentname),max(c.categorytype) from content c where CategoryType like 'TRUETONES' and Status=1 group by c.ContentId limit 5) ) order by CategoryType,SID desc");
        queries.put("GETVDO", "Select SeqId,OriginalFile,Price from content where ContentId='${contentID}' and CategoryId='${categoryID}' and Status=1");
        queries.put("GETAW", "Select SeqId,OriginalFile,Price from content where ContentId='${contentID}' and CategoryId='${categoryID}' and Status=1 limit 1");
        queries.put("GETCONTENTDATA", "Select SeqId,ContentId,ContentName,CategoryId,CategoryName,OriginalFile,Price,Status,ProviderId,ContentImage,Format,Width,Height,AspectRatio,Singerorfilm,ServiceURL,Parameters,jspf,OrderSequence,Insertiontime,CategoryType,Description,SampleFile,ProvidercontentId, Point1, Point2, Bias, FillColor FROM content where SeqID='${SeqID}' and Status=1;");

        queries.put("SAVESENDXMSWAP", "INSERT INTO spoolDir (provider, created, priority, xms) SELECT 1, NOW(), 1, '${sendxmswap}' FROM providerId WHERE name='${providerName}'");
        queries.put("SAVESENDXMS", "INSERT INTO spoolDir (provider, created, priority, xms) SELECT 1, NOW(), 1, '${sendxms}' FROM providerId WHERE name='${providerName}'");

        // BY: USMAN
        queries.put("UPDATEDOWNLOADSTATUS", "Update downloads set Status = ${Status} WHERE ContentId='${ContentID}'AND MSISDN='${GSM}' AND TID = ${TID} AND Status < 2");
        queries.put("UPDATETRANSACTIONSTATUS", "Update downloads set TStatus = ${TStatus} WHERE ContentId='${ContentID}'AND MSISDN='${GSM}' AND TID = ${TID}");
        queries.put("GETDOWNLOADDETAILS", "SELECT Status, TID, PurchaseTime FROM downloads WHERE ContentId='${ContentID}' AND MSISDN='${GSM}' AND CategoryType='${CategoryType}' AND PurchaseTime >= (NOW() - INTERVAL 1 DAY) AND Status < 2 AND TStatus = 0;");
        queries.put("getContentIdForContentFile", "SELECT DISTINCT ContentId FROM handsetcontent WHERE ContentFile LIKE '%${ContentFile}%';");
        queries.put("GETUNCOMMITTEDTRANSACTIONS", "SELECT TID, ContentID, MSISDN FROM downloads where PurchaseTime >= (NOW() - INTERVAL 1 DAY) AND TID > 0 AND TStatus = 0 AND Status = 2;");
        queries.put("UPDATEDOWNLOADCOUNTS", "UPDATE content SET DownloadTimes = DownloadTimes + 1 WHERE ContentId ='${ContentId}'");
        // PurchaseTime >= (NOW() - INTERVAL 1 DAY)
        queries.put("RANDOM_BANNER", "SELECT SeqID, AddName, AddGraphics, Jspf, Tags FROM adds WHERE Status = 1 AND IsBanner = 1 AND (Tags like ${tags}) order by RAND() limit 1;");

        //-----------------//
        queries.put("getRandomBannerRowNum",
                "SELECT FLOOR(RAND() * COUNT(*)) as rand_row FROM adds "
                + "WHERE Status = 1 AND IsBanner = 1 AND (Tags like ${tags});");
        queries.put("getRandomBanner",
                "SELECT SeqID, AddName, AddGraphics, Jspf, Tags FROM adds "
                + "WHERE Status = 1 AND IsBanner = 1 AND (Tags like ${tags}) order by RAND() limit ${limit},1;");
        queries.put("BannerTracker", "INSERT INTO banner_clicks(MSISDN,BannerId,TStamp,BannerName) Values('${GSM}','${BID}',NOW(),'${BannerName}')");
        queries.put("GetBannerJspf", "SELECT Jspf, AddName From adds WHERE SeqId='${SeqId}' LIMIT 1");

        vecMetaDataColumnLabels = new Vector<String>();
    }

    public static DBProc getInstance() {
        if (dbProc == null) {
            dbProc = new DBProc();
        }
        return dbProc;
    }

    public Vector executeSQLMethod(String methodName, Map params) {
        return (Vector) executeSQLonConn(methodName, params, 0);
    }

    /**
     * ArrayList implementation of executeSQLMethod(String, String)
     * @param methodName
     * @param params
     * @return ArrayList of ArrayList
     */
    public ArrayList executeSQLMethod_AL(String methodName, Map params) {
        return (ArrayList) executeSQLonConn(methodName, params, 2);
    }

    /**
     * <pre>
     * Execute External query.
     * </pre>
     * @param query
     * @param params
     * @return
     */
    public Vector executeDirectSQL(String query, Map params) {
        return (Vector) executeDirectSQLonConn(query, params, 0);
    }

    /**
     * Not to be used, until you are certain that the passed params are checked for SQL injection
     * @param methodName
     * @param params
     * @param skipSQLcheck
     * @return Vector
     */
    public Vector executeSQLMethod(String methodName, Map params, boolean skipSQLcheck) {
        return (Vector) executeSQLonConn(methodName, params, 0, skipSQLcheck);
    }

    public RowSetDynaClass executeRSSQLMethod(String methodName, Map params) {
        return (RowSetDynaClass) executeSQLonConn(methodName, params, 1);
    }

    public Object executeSQLonConn(String methodName, Map params, int retType) {
        return executeSQLonConn(methodName, params, retType, false);
    }

    public Object executeSQLonConn(String methodName, Map params, int retType, boolean skipSQLcheck) {
        Vector<Object> vecResult = null;
        String query = null;
        try {
            vecResult = new Vector<Object>();
            if((query = queries.get(methodName)) != null) {
                Set ks = params.keySet();
                for (Iterator it = ks.iterator(); it.hasNext();) {
                    Object obj = it.next();
                    if (skipSQLcheck) {
                        query = query.replace("${" + obj.toString() + "}", params.get(obj).toString());
                    } else {
                        query = query.replace("${" + obj.toString() + "}", formatSQLString(params.get(obj).toString()));
                    }
                }
                vecResult.add(query);
            }
            return executeSQLsHelper(methodName, params, retType, vecResult);
        } catch (Exception e) {
            log.error(methodName + ": " + query, e);
        }
        return null;
    }

    /**
     * <pre>
     * this enables DBProc to accept queries from outside DBProc.
     * BY: USMAN
     * </pre>
     *
     * @param conn
     * @param query
     * @param params
     * @param retType
     * @return
     */
    public Object executeDirectSQLonConn(String query, Map params, int retType) {
        Vector<Object> vecResult = null;
        try {
            vecResult = new Vector<Object>();
            Set ks = params.keySet();
            for (Iterator it = ks.iterator(); it.hasNext();) {
                Object obj = it.next();
                query = query.replace("${" + obj.toString() + "}", formatSQLString(params.get(obj).toString()));
            }
            vecResult.add(query);
            return executeSQLsHelper("", params, retType, vecResult);
        } catch (Exception e) {
            log.error("Direct Query: " + query, e);
        }
        return null;
    }

    private Object executeSQLsHelper(String methodName, Map params, int retType, Vector vecQueries) {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rsResult = null;

        Object result = null;
        boolean success = true;
        Vector<Object> vecResult = null;
        ArrayList<Object> arryResult = null;
        String query = null;
        int retAutoGen = -1;
        int queryCounter = 0;
        if (vecMetaDataColumnLabels != null) {
            vecMetaDataColumnLabels.clear();
        }

        try {
            conn = getJNDIConnection();
            for (Iterator it = vecQueries.iterator(); it.hasNext();) {
                queryCounter++;
                query = it.next().toString().trim();
                log.debug("query[" + queryCounter + "]: " + query);
                if (query.toUpperCase().startsWith("SELECT") || query.toUpperCase().startsWith("(SELECT")
                        || query.toUpperCase().startsWith("IF") || query.toUpperCase().startsWith("/*SELECT*/")) {

                    try {
                        stmt = conn.createStatement();
                        rsResult = stmt.executeQuery(query);
                        if (rsResult.next()) {
                            rsResult.beforeFirst();
                            if (retType == 0) {
                                result = formatVector(rsResult);
                                ResultSetMetaData rsmd = rsResult.getMetaData();
                                int size = rsmd.getColumnCount();
                                if (size > 0) {
                                    for (int idx = 1; idx <= size; idx++) {
                                        vecMetaDataColumnLabels.add(rsmd.getColumnLabel(idx));
                                    }
                                }
                            } else if (retType == 1) {
                                result = new RowSetDynaClass(rsResult);
                            } else if (retType == 2) {
                                result = formatArrayList(rsResult);
                                ResultSetMetaData rsmd = rsResult.getMetaData();
                                int size = rsmd.getColumnCount();
                                if (size > 0) {
                                    for (int idx = 1; idx <= size; idx++) {
                                        vecMetaDataColumnLabels.add(rsmd.getColumnLabel(idx));
                                    }
                                }
                            }
                        }
                        rsResult.close();
                        stmt.close();
                    } catch (Exception ex) {
                        log.error("Execute SQLsHelper Exception ", ex);
                        success = false;
                    }
                } else {
                    try {
                        stmt = conn.createStatement();
                        int retUpdate = stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
                        if (query.toUpperCase().startsWith("INSERT")) {
                            rsResult = stmt.getGeneratedKeys();
                            if (rsResult.next()) {
                                try {
                                    retAutoGen = Integer.parseInt(rsResult.getString(1));
                                } catch (Exception ex) {
                                    retAutoGen = -1;
                                }
                            }
                            rsResult.close();
                        } else if (query.toUpperCase().startsWith("UPDATE") || query.toUpperCase().startsWith("CALL")) {
                            retAutoGen = retUpdate;
                        }
                        stmt.close();
                    } catch (Exception ex) {
                        success = false;
                        retAutoGen = -1;
                        log.error("", ex);
                    } finally {
                        if (retType == 0) {
                            vecResult = new Vector<Object>();
                            vecResult.add(new Boolean(success));
                            vecResult.add(new Integer(retAutoGen));
                            result = vecResult;
                        } else if (retType == 1) {
                            result = null;
                        } else if (retType == 2) {
                            arryResult = new ArrayList<Object>();
                            arryResult.add(new Boolean(success));
                            arryResult.add(new Integer(retAutoGen));
                            result = arryResult;
                        }
                    }
                }
            } //end for loop here
        } catch (Exception e) {
            log.error(methodName + ": " + query, e);
        } finally {
            closeJNDIConnection(conn);
        }
        return result;
    }

    public Connection getJNDIConnection() throws ConnectionNotFoundException {
        Connection result = null;
        try {
            // Class.forName("com.mysql.jdbc.Driver");
            // databaseConnection = DriverManager.getConnection(strDBConn,
            // strDBUser, strDBPass);
            Context initContext = new InitialContext();
            Context envContext = (Context) initContext.lookup("java:/comp/env");
            DataSource ds = (DataSource) envContext.lookup("jdbc/UfoneDB");
            result = ds.getConnection();
        } catch (NamingException ex) {
            throw new ConnectionNotFoundException(ex);
        } catch (SQLException ex) {
            throw new ConnectionNotFoundException(ex);
        }
//        connCount++;
//		log.info("getJNDIConnection, Active connections: "+connCount);
        return result;
    }

    public void closeJNDIConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                conn = null;
//                connCount--;
//				log.info("closeJNDIConnection, Active connections: "+connCount);
            } catch (SQLException e) {
                log.error("Error closing connection, connCount:" + connCount, e);
            }
        }
    }

    private Vector<Object> formatVector(ResultSet rsData) {

        Vector<Object> vecOld = null;
        try {
            vecOld = new Vector<Object>();
            int cols = rsData.getMetaData().getColumnCount();
            while (rsData.next()) {
                Vector<Object> vecRow = new Vector<Object>();
                for (int i = 1; i <= cols; i++) {
                    vecRow.add(rsData.getObject(i));
                }
                vecOld.add(vecRow);
            }
        } catch (Exception ex) {
            log.error("Error populating vector from db data", ex);
            vecOld = null;
        }
        return vecOld;
    }

    /**
     * <pre>
     * prepares the result set to be returned as ArrayList.
     *
     * By: Usman Malik
     * May 11, 2009
     * </pre>
     *
     * @param rsData
     * @param arryOld
     * @return
     */
    private ArrayList<Object> formatArrayList(ResultSet rsData) {

        ArrayList<Object> arryOld = null;
        try {
            arryOld = new ArrayList<Object>();
            int cols = rsData.getMetaData().getColumnCount();
            while (rsData.next()) {
                ArrayList<Object> arryRow = new ArrayList<Object>();
                for (int i = 1; i <= cols; i++) {
                    arryRow.add(rsData.getObject(i));
                }
                arryOld.add(arryRow);
            }
        } catch (Exception ex) {
            arryOld = null;
        }
        return arryOld;
    }

    private String formatSQLString(String strQuery) {
        String strFormatedString = strQuery;

        strFormatedString = strFormatedString.replace("\'", "''");
        // strFormatedString = strFormatedString.replace("%", "");
        // strFormatedString = strFormatedString.replace("&", " ");
        // strFormatedString = strFormatedString.replace(";", " ");

        return strFormatedString;
    }

    public Vector getMetaDataColumnLabels() {
        return vecMetaDataColumnLabels;
    }
    /*--
    public Vector getTopContent(){
    Vector v=new Vector();
    Vector vReturn=new Vector();
    Vector v_subcat=null;
    try{
    String dbCate="";
    String cat="-1";
    String strQuery="SELECT * FROM ((SELECT Distinct ContentID as contid, ContentName as contname , ContentImage as contimg, Price as contprice, ServiceURL as contserURL, jspf AS JSP, html AS HTM, Parameters as params, Status, SingerOrFilm, CategoryType As ContType, CategoryId As CategoryID, MainCat As MainCat,ContentSequence,orderSequence, updatedat FROM content  WHERE (Tags LIKE '%%' OR ContentName LIKE '%%') AND CategoryID = 1 and MainCat like '%Bollywood%' and SingerOrFilm like '%%' and Status = 1 AND IsAuthorized = 1 order by ContentSequence desc, updatedat desc limit 10) UNION ALL (SELECT Distinct ContentID as contid, ContentName as contname , ContentImage as contimg, Price as contprice, ServiceURL as contserURL, jspf AS JSP, html AS HTM, Parameters as params, Status, SingerOrFilm, CategoryType As ContType, CategoryId As CategoryID, maincat as maincat,ContentSequence,orderSequence, updatedat FROM content  WHERE (Tags LIKE '%%' OR ContentName LIKE '%%') AND CategoryID = 3 and MainCat like '%Bollywood%' and SingerOrFilm like '%%' and Status = 1 AND IsAuthorized = 1 order by ContentSequence desc, updatedat desc limit 10) UNION ALL (SELECT Distinct ContentID as contid, ContentName as contname , ContentImage as contimg, Price as contprice, ServiceURL as contserURL, jspf AS JSP, html AS HTM, Parameters as params, Status, SingerOrFilm, CategoryType As ContType, CategoryId As CategoryID, maincat as maincat,ContentSequence,orderSequence, updatedat FROM content  WHERE (Tags LIKE '%%' OR ContentName LIKE '%%') AND CategoryID = 4 and MainCat like '%Bollywood%' and SingerOrFilm like '%%' and Status = 1 AND IsAuthorized = 1 order by ContentSequence desc, updatedat desc limit 10)) AS res ORDER BY orderSequence asc, ContentSequence desc";
    Connection con = dbProc.getJNDIConnection();
    PreparedStatement pst=con.prepareStatement(strQuery);
    ResultSet rs = pst.executeQuery();
    v= this.formatVector(rs);
    dbProc.closeJNDIConnection(con);

    for(int i=0;i<=v.size();i++){

    if(i<v.size()){
    dbCate  = ((Vector)v.get(i)).get(10).toString();
    }else{
    cat="Finish";
    }
    if (!dbCate.equals(cat)) {
    if (!"-1".equals(cat)) {
    vReturn.add(v_subcat);
    }
    v_subcat = new Vector();
    }
    if(i<v.size()){
    v_subcat.add((Vector)v.get(i));
    cat = dbCate;
    }

    }

    }catch(Exception ex){
    log.debug("getTopContent : ",ex);
    return new Vector();
    }
    return  vReturn;
    }
    --**/

    public Vector<Object> getMoreContent(String pageNo, String catid) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_getcontent(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setString(2, catid);
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getMoreContent PageNo=" + pageNo + " CategoryId=" + catid, e);
            return new Vector();
        } finally {
            closeJNDIConnection(conn);
        }
        return v;
    }

    /**
     * getContent SubCategories
     * @return
     */
    public Vector getContentCat() {
        HashMap hm = null;
        Vector v=new Vector();
        Connection con = null;
        try {
            hm = new HashMap();
            //"SELECT TopCatName,Group_Concat(CategoryName) FROM categories where status=1 and TopCatName IN (select distinct CategoryName from categories where status=1 and TopCatName='Ufone_Content') group by maincat";
            String strQuery = "select PageTitle from categories where status=1 and TopCatName='Ufone_Content'";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(rs.getString(1));
            }
            rs.close();
            pst.close();
            
        } catch (Exception ex) {
            log.debug(" getContentCat() ", ex);
            return null;
        } finally {
            closeJNDIConnection(con);
        }
        return v;
    }

    public Vector getServices(String cat_id) throws SQLException {
        Connection con = null;
        PreparedStatement pst = null;
        Vector v = new Vector();
        try {
            //SELECT ucat.name,uservice.title,uservice.imgurl,uservice.href,uservice.id,uservice.title_ur FROM uservice_categories ucat,uservices uservice where (uservice.isactive=1 AND ucat.isactive=1) AND ucat.id=uservice.cate_id Order by ucat.priority,uservice.priority
            //SELECT id,title, imgurl, href,details, title_ur, details_ur FROM uservices where cate_id=2 and isactive=1 ORDER BY priority ASC
            String strQuery = "SELECT id,title, imgurl, href,title_ur FROM uservices where cate_id=? and isactive=1 ORDER BY priority ASC";
            con = getJNDIConnection();
            pst = con.prepareStatement(strQuery);
            pst.setInt(1, Integer.parseInt(cat_id));
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {//id,title, imgurl, href,details, title_ur, details_ur
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
            }
            rs.close();
        } catch (Exception e) {
            log.error("getServices :", e);
            v.clear();
        } finally {
            if (pst != null) {
                pst.close();
            }
            closeJNDIConnection(con);
        }
        return v;
    }

    public String[] getServiceContent(String id) throws SQLException {
        Connection con = null;
        PreparedStatement pst = null;
        String[] arrResult = null;
        try {
            String strQuery = "SELECT title,imgurl,href,details,title_ur,details_ur FROM uservices where id=?";
            con = getJNDIConnection();
            pst = con.prepareStatement(strQuery);
            pst.setString(1, id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                arrResult = new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)};
            }
            rs.close();
        } catch (Exception e) {
            log.error("getServiceContent : Service Id =" + id + " :", e);
            return null;
        } finally {
            if (pst != null) {
                pst.close();
            }
            closeJNDIConnection(con);
        }
        return arrResult;
    }

    public Vector<Object> getHadith(String pageNo, int arg) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_hadiths(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setInt(2, arg);
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;

            while (rs.next()) {
                if (arg == 0) {
                    v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3)});
                } else {
                    v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)});
                }
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getHadith PageNo=" + pageNo + " agr=" + arg, e);
            v.clear();
        } finally {
        	closeJNDIConnection(conn);
        }
        return v;
    }

    public Vector<Object> getQuran(String pageNo, int arg) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_quran(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setInt(2, arg);
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;

            while (rs.next()) {
                if (arg == 0) {
                    v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3)});
                } else {
                    v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)});
                }
            }
            log.debug("Found for page(" + pageNo + "): v Size " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getQuran PageNo=" + pageNo + " agr=" + arg, e);
            v.clear();
        } finally {
        	closeJNDIConnection(conn);
        }
        return v;
    }

    public Vector<Object> getRSSNews(String feed_id, int limt) {
        Connection con = null;
        Vector<Object> vResult = new Vector<Object>();
        try {
            String strQuery = "SELECT id,channel_id,headline,imgpath,newsdate FROM rss_items where channel_id=? AND newsdate>=date_sub(now(),Interval 30 month) AND isactive=1 order by newsdate desc LIMIT 0, ?";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, feed_id);
            pst.setInt(2, limt);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                vResult.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
            }
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getCricketNews() ", e);
            vResult = null;
        } finally {
            closeJNDIConnection(con);
        }
        return vResult;
    }

    public Vector getNewsDetail(String news_id) {
        Vector v = new Vector();
        
        Connection con = null;
        try {
            String strQuery = "SELECT channel_id, headline, date_format(newsdate, '%Y-%m-%d %H:%i:%s') newsdate, details, imgpath FROM rss_items where isactive=1 AND id=?";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setInt(1, Integer.parseInt(news_id));

            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(rs.getString(1));
                v.add(rs.getString(2));
                v.add(rs.getString(3));
                v.add(rs.getString(4));
                v.add(rs.getString(5));
            }
            pst.close();

        } catch (Exception e) {
            log.error("getNewsDetail(" + news_id + ") ", e);
            v = null;
        } finally {
            closeJNDIConnection(con);
        }
        return v;
    }

    public int[] getNextPrevIds(String chId, String newsId) {
        int[] returnId = {0, 0};
        
        Connection con = null;
        try {
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement("SELECT id FROM rss_items WHERE channel_id=? AND newsdate>=date_sub(now(),Interval 30 day) AND isactive=1 and newsdate > (select r.newsdate from rss_items r where r.id=?) ORDER BY newsdate asc limit 1");
            try {
                pst.setInt(1, Integer.parseInt(chId));
                pst.setInt(2, Integer.parseInt(newsId));
                ResultSet rs = pst.executeQuery();
                if (rs.next()) {
                    returnId[0] = rs.getInt(1);
                }
                pst.close();
                pst = null;
            } catch (Exception e) {
            }
            pst = con.prepareStatement("SELECT id FROM rss_items WHERE channel_id=? AND newsdate>=date_sub(now(),Interval 30 day) AND isactive=1 and newsdate < (select r.newsdate from rss_items r where r.id=?) ORDER BY newsdate desc limit 1");
            pst.setInt(1, Integer.parseInt(chId));
            pst.setInt(2, Integer.parseInt(newsId));
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                returnId[1] = rs.getInt(1);
            }
            pst.close();
            pst = null;
        } catch (Exception e) {
            log.error("hasNextNews(" + newsId + ") ", e);

        } finally {
            closeJNDIConnection(con);
        }
        return returnId;
    }

    public Vector<Object> getNewsFeed(String pageNo, String strfeed_id) {
        Vector<Object> v = new Vector<Object>();
        
        Connection conn = null;
        try {

            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_news_feed(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setInt(2, Integer.parseInt(strfeed_id));
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getNewsFeed pageNo=" + pageNo + " strfeed_id=" + strfeed_id, e);
            v = null;
        } finally {

        	closeJNDIConnection(conn);
        }
        return v;
    }

    public Vector<Object> getCoolStuff(String str_stuff,String str_stuff_id) {
        Connection con = null;
        String strQuery = "";
        Vector<Object> v = new Vector<Object>();
        try {
            String strStuff=str_stuff;
            if ("Top Hollywood Movies".equals(strStuff) || "Top Bollywood Movies".equals(strStuff) || "Top Music Charts English".equals(strStuff) || "Top Music Charts Urdu".equals(strStuff) || "Hollywood Gossip".equals(strStuff) ||"Bollywood Gossip".equals(strStuff)||"Lollywood Gossip".equals(strStuff)) {
                strStuff = strStuff.replace("Top Hollywood Movies", "Hollywood");
                strStuff = strStuff.replace("Top Bollywood Movies", "Bollywood");
                strStuff = strStuff.replace("Top Music Charts English", "Top English Songs");
                strStuff = strStuff.replace("Top Music Charts Urdu", "Top Urdu Songs");

                strStuff = strStuff.replace("Hollywood Gossip", "HOLLYWOOD BUZZ");
                strStuff = strStuff.replace("Bollywood Gossip", "BOLLYWOOD BUZZ");
                strStuff = strStuff.replace("Lollywood Gossip", "LOLLYWOOD BUZZ");

                strQuery = "SELECT ContentData, ContentData_UR FROM quotesnjokes where Category=? AND Status=1 order by SaveDate desc LIMIT 1";
            } else if("Love Quotes".equals(strStuff)||"Famous Quotes".equals(strStuff)){

            }else if ("Born Today".equals(strStuff)) {
                strQuery = "SELECT date_format(SaveDate, '%M, %d'),ContentData FROM quotesnjokes where Category=? AND Status=1 order by SaveDate desc LIMIT 1";
            }else if(str_stuff_id.length()>0){
            strQuery = "SELECT ContentData, ContentData_UR FROM quotesnjokes where SeqID=? AND Status=1 LIMIT 1";
            strStuff=str_stuff_id;
            }else{//this query execute for Jokes 
                strQuery = "SELECT ContentData, ContentData_UR FROM quotesnjokes where Category=? AND Status=1 order by Rand() limit 1";
            }
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, strStuff);
            ResultSet rs = pst.executeQuery();
            
        	while (rs.next()) {
                v.add(new  String[]{rs.getString(1),rs.getString(2)});
                
            }
            
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getCoolStuff(" + str_stuff + " ) ", e);
            v = null;
        } finally {
        	closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> getQuotes(String str_stuff_id) {
        Connection con = null;
        String strQuery = "";
        Vector<Object> v = new Vector<Object>();
        try {

            strQuery = "SELECT headline,details FROM rss_items where channel_id=? and isactive=1 ORDER BY newsdate DESC Limit 4";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, str_stuff_id);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(new  String[]{rs.getString(1),rs.getString(2)});

            }
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getQuotes(" + str_stuff_id + " ) ", e);
            v = null;
        } finally {
        	closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> getNewsSearch(String pageNo, String strSrh) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_getsearchnews(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setString(2, "%" + strSrh + "%");
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getNewsSearch PageNo=" + pageNo + " StringSearch=" + strSrh, e);
            v=null;
        } finally {
        	closeJNDIConnection(conn);
        }
        return v;
    }
    public Vector<Object> getServicesSearch(String pageNo,String strSrh) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_getsearch_services(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setString(2, "%" + strSrh + "%");
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getNewsSearch PageNo=" + pageNo + " StringSearch=" + strSrh, e);
            v=null;
        } finally {
        	closeJNDIConnection(conn);
        }
        return v;
    }
    public Vector<Object> getFunSearch(String pageNo, String strSrh) {
        Vector<Object> v = new Vector<Object>();

        Connection conn = null;
        try {
            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_search_fun(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setString(2, "%" + strSrh + "%");
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            int c = 0;
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4)});
            }
            log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getNewsSearch PageNo=" + pageNo + " StringSearch=" + strSrh, e);
            v=null;
        } finally {
        	closeJNDIConnection(conn);
        }
        return v;
    }
    public Vector<Object> get_namz_timing_cities(){
        Connection con=null;
        Vector<Object> v_cities=new Vector<Object>();
        try{
            String strQuery="SELECT City, CONCAT(LatD, '.', LatM) AS Latitude, SeaLevel, CONCAT(LongD, '.', LongM) AS Longitude FROM cities_index  WHERE Status = 1 AND ForIslamicTimings = 1";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                v_cities.add(new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4)});
            }
            rs.close();
            pst.close();
        }catch(Exception e){
            log.error("get_namz_timing_cities()",e);
            v_cities=null;
        }finally{
        	closeJNDIConnection(con);
        }
        return v_cities;
    }
    public Vector<Object> get_stock_index(String stock_type){
        Connection con=null;
        Vector<Object> v=new Vector<Object>();
        try{
            String str_query="SELECT i.current, i.volume, i.value, i.high, i.low, i.change FROM stock_index i where i.index=?";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(str_query);
            pst.setString(1, stock_type);
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                v.add(new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6)});
            }
        }catch(Exception e){
            log.error("get_stock_index() ",e);
        }finally{
           closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> get_stock_market(String stock_type){
        Connection con=null;
        Vector<Object> v=new Vector<Object>();
        try{
            String str_query="SELECT m.flag, m.bid_vol, m.bid_price, m.offer_price, m.offer_vol, m.last_rate, m.change, m.total_vol FROM stock_updates m where m.symbol=?";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(str_query);
            pst.setString(1, stock_type);
            ResultSet rs = pst.executeQuery();
            while(rs.next()){
                v.add(new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8)});
            }
        }catch(Exception e){
            log.error("get_stock_market(" +stock_type+" ) ",e);
            v=null;
        }finally{
            closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> get_stock_market_symbol(){
        Connection con=null;
        Vector<Object> v=new Vector<Object>();
        try{
            String str_query="SELECT symbol FROM stock_updates";
            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(str_query);
            ResultSet rs = pst.executeQuery();

            while(rs.next()){
                v.add(rs.getString(1));
            }
            rs.close();
            pst.close();
        }catch(Exception e){
            log.error("get_stock_market_symbols() ",e);
            v=null;
        }finally{
            closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> getHoroscope(String str_stuff) {
        Connection con = null;
        String strQuery = "";
        Vector<Object> v = new Vector<Object>();
        try {
            
            strQuery = "SELECT Html,CategoryName FROM content where CategoryID=? AND Status=1 LIMIT 1";

            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, str_stuff);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(new  String[]{rs.getString(1),rs.getString(2)});
                
            }
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getHoroscope(" + str_stuff + " ) ", e);
            v = null;
        } finally {
        	closeJNDIConnection(con);
        }
        return v;
    }
    public Vector<Object> getLoveHoroscope(String str_stuff) {
        Connection con = null;
        String strQuery = "";
        Vector<Object> v = new Vector<Object>();
        try {

            strQuery = "SELECT description FROM love_horoscope where star=?";

            con = getJNDIConnection();
            PreparedStatement pst = con.prepareStatement(strQuery);
            pst.setString(1, str_stuff);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(new  String[]{rs.getString(1)});

            }
            rs.close();
            pst.close();
        } catch (Exception e) {
            log.error("getLoveHoroscope(" + str_stuff + " ) ", e);
            v = null;
        } finally {
            closeJNDIConnection(con);
        }
        return v;
    }//
    public Vector<Object> getContentCat(String pageNo, String str_main_cat) {
        Vector<Object> v = new Vector<Object>();
        Connection conn = null;
        try {

            conn = getJNDIConnection();
            CallableStatement cs = conn.prepareCall("{ CALL proc_get_content_cat(?,?,?,?) }");
            cs.setInt(1, Integer.parseInt(pageNo));
            cs.setString(2, str_main_cat);
            cs.registerOutParameter(3, java.sql.Types.INTEGER);
            cs.registerOutParameter(4, java.sql.Types.INTEGER);
            ResultSet rs = cs.executeQuery();
            int count = cs.getInt(3);
            int rec = cs.getInt(4);
            log.debug("Total Records: " + count);

            v.add(count);
            v.add(rec);

            
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2)});
            }
            rs.close();
            //log.debug("Found for page(" + pageNo + "): " + v.size());
            cs.close();
        } catch (Exception e) {
            log.error("getContentCat pageNo=" + pageNo + " strfeed_id=" + str_main_cat, e);
            v = null;
        } finally {

        	closeJNDIConnection(conn);
        }
        return v;
    }
      public Vector getServicesCategoryz() throws SQLException {
        Connection con = null;
        PreparedStatement pst = null;
        Vector v = new Vector();
        try {
            String strQuery = "SELECT id,name,icon_name,format FROM uservice_categories where isactive=1 ORDER BY priority ASC";
            con = getJNDIConnection();
            pst = con.prepareStatement(strQuery);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                v.add(new String[]{rs.getString(1), rs.getString(2),rs.getString(3), rs.getString(4)});
            }
            rs.close();
        } catch (Exception e) {
            log.error("getServices :", e);
            v.clear();
        } finally {
            if (pst != null) {
                pst.close();
            }
            closeJNDIConnection(con);
        }
        return v;
    }
    public Vector getWeatherDetail(String city) {
        Vector vReturn = null;
        Connection conn = null;
        try {
            vReturn = new Vector();
            conn= getJNDIConnection();
            PreparedStatement pst = conn.prepareStatement("SELECT n.channel_id, n.headline, n.details, date_format(n.newsdate, '%Y-%m-%d %H:%i:%s') newsdate FROM rsschannels r inner join rss_items n on r.id=n.channel_id AND n.isactive=1 where r.name=? limit 1");
            pst.setString(1, "weather"+city);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                vReturn.add(rs.getString(1));
                vReturn.add(rs.getString(2));
                vReturn.add(rs.getString(3));
                vReturn.add(rs.getString(4));
                log.debug("weather found for: weather"+city+", "+rs.getString(3));
            }
            rs.close();
            pst.close();
            
        } catch (Exception e) {
            log.error("Error in getWeatherDetail : " + city, e);
        }
        finally{
        	closeJNDIConnection(conn);
        }
        return vReturn;
    }
    public Vector getWeatherCities(){
         Connection con=null;
         Vector<Object> v=new Vector<Object>();
         try{
             String strQuery="SELECT City FROM cities_index WHERE Status=1 AND ForWeather = 1 ORDER BY rank";
             con = getJNDIConnection();
             PreparedStatement pst = con.prepareStatement(strQuery);
             ResultSet rs = pst.executeQuery();
             while(rs.next()){
                 v.add(rs.getString(1));
             }
             rs.close();
             pst.close();

         }catch(Exception e){
             log.error("getWeatherCities",e);
             v.clear();
         }finally{
        	 closeJNDIConnection(con);
         }
         return v;

     }

    public String getLiveScore() {
    	String livescore = "";
        Connection con = null;
        try {
            con = getJNDIConnection();
            String str_sql = "select smstext from cri_livescore order by tstamp desc limit 1";            
            PreparedStatement pst=con.prepareStatement(str_sql);
            ResultSet rs = pst.executeQuery();
            if (rs.next()) {
                livescore = rs.getString(1);
            }
            rs.close();
            pst.close();

        } catch (Exception ex) {
            log.error("ERROR: getLiveScore", ex);
        } finally {
            closeJNDIConnection(con);
        }
        return livescore;
    }
	 
    public Vector<String[]> getMatchHighlights() {
        Vector<String[]> result = new Vector<String[]>();
        Connection con = null;
        String str_sql="";

        try {
            con = getJNDIConnection();
            str_sql = "SELECT eventid, eventtype, smstext, teamA, teamB FROM cric_events e where date_format(e.tstamp,'%Y-%m-%d')=current_date() order by seqid desc";
            PreparedStatement pst = con.prepareStatement(str_sql);

            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                result.add(new String[]{rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)});
            }
            rs.close();
            pst.close();

        } catch (Exception ex) {
            log.error("ERROR:Allevents", ex);

        } finally {
            closeJNDIConnection(con);
        }

        return result;

    }
    public Vector<String> getRbtDetails(String strRbtId) {
        Vector<String> result = new Vector<String>();
        Connection con = null;
        String str_sql="";

        try {
            con = getJNDIConnection();
            str_sql = "SELECT Category, Subcategory,Title,Movie_Album,Singer FROM utunes where isActive=1 and RBT_Code=?";
            PreparedStatement pst = con.prepareStatement(str_sql);
            pst.setString(1,strRbtId);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                result.add(rs.getString(1));
                result.add(rs.getString(2));
                result.add(rs.getString(3));
                result.add(rs.getString(4));
                result.add(rs.getString(5));
                
            }
            rs.close();
            pst.close();

        } catch (Exception ex) {
            log.error("getRbtDetails", ex);

        } finally {
            closeJNDIConnection(con);
        }

        return result;

    }
    public Vector<String[]> getTopRbts(int intNew, int intDownload) {
        Vector<String[]> result = new Vector<String[]>();
        Connection con = null;
        String str_sql="";

        try {
            con = getJNDIConnection();
            str_sql = "SELECT RBT_Code,Title FROM utunes where isActive=1 and is_most_downloaded=? and is_new=? order by priority desc limit 10";
            PreparedStatement pst = con.prepareStatement(str_sql);
            pst.setInt(1, intDownload);
            pst.setInt(2, intNew);
            ResultSet rs = pst.executeQuery();
            while (rs.next()) {
                result.add(new String[]{rs.getString(1),rs.getString(2)});
            }
            rs.close();
            pst.close();

        } catch (Exception ex) {
            log.error("getTopRbts", ex);
        } finally {
            closeJNDIConnection(con);
        }
        return result;
    }
}
